var express = require('express');
var Msdb = require("../../../db/Mysqldb.js")
var router = express.Router()   //可使用 express.Router 类创建模块化、可挂载的路由句柄

var db = new Msdb()
var getTime=()=>{
    return new Date(new Date().getTime()+8 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');//获取系统时间并转为数据库接受的格式
}

router.post("/getStations", async function(req, res) {
    var result = {};
    var data = req.body;
    try {
        // 基础SQL语句 - 支持关联客户表
        let query = "SELECT s.*";
        let countQuery = "SELECT COUNT(*) AS total FROM station s";
        let params = [];
        let whereConditions = [];
        let joinClause = ""; // 关联查询条件
        
        // 判断是否需要关联查询客户信息
        const includeCustomer = data.includeCustomerInfo === true;
        if (includeCustomer) {
            // 关联客户表并添加别名，避免字段冲突
            joinClause = " LEFT JOIN customer c ON s.customer_id = c.id";
            // 补充查询客户信息（使用别名区分）
            query += ", c.id AS customer_id, c.name AS customer_name, c.phone AS customer_phone, " +
                     "c.insert_user_id AS customer_insert_user, c.create_time AS customer_create_time, " +
                     "c.update_user_id AS customer_update_user, c.update_time AS customer_update_time";
        }
        
        // 多条件筛选处理
        // 1. 通过站点ID精确查询
        if (data.id !== undefined && data.id !== null && !isNaN(Number(data.id))) {
            whereConditions.push("s.id = ?");
            params.push(Number(data.id));
        }
        
        // 2. 站点名称模糊查询
        if (data.name && data.name.trim() !== "") {
            whereConditions.push("s.name LIKE ?");
            params.push(`%${data.name.trim()}%`);
        }
        
        // 3. 客户ID筛选
        if (data.customer_id !== undefined && data.customer_id !== null && !isNaN(Number(data.customer_id))) {
            whereConditions.push("s.customer_id = ?");
            params.push(Number(data.customer_id));
        }
        
        // 拼接SQL语句
        query += " FROM station s" + joinClause; // 添加表名和关联条件
        if (whereConditions.length > 0) {
            query += " WHERE " + whereConditions.join(" AND ");
            countQuery += " WHERE " + whereConditions.join(" AND ");
        }
        
        // 分页处理
        const usePagination = data.pageSize !== undefined && data.pageSize !== null 
            && data.offset !== undefined && data.offset !== null
            && !isNaN(Number(data.pageSize)) && !isNaN(Number(data.offset));
            
        if (usePagination) {
            query += " LIMIT ? OFFSET ?";
            params.push(Number(data.pageSize), Number(data.offset));
        }
        
        // 执行查询
        var stations = await db.query(query, params);
        
        // 处理返回结果 - 格式化客户信息
        if (includeCustomer) {
            stations = stations.map(station => {
                // 将客户相关字段提取为单独的customer对象
                const customer = {
                    id: station.customer_id,
                    name: station.customer_name,
                    phone: station.customer_phone,
                    insert_user_id: station.customer_insert_user,
                    create_time: station.customer_create_time,
                    update_user_id: station.customer_update_user,
                    update_time: station.customer_update_time
                };
                
                // 删除原始对象中的客户字段，避免冗余
                const { 
                    customer_id, customer_name, customer_phone,
                    customer_insert_user, customer_create_time,
                    customer_update_user, customer_update_time,
                    ...stationInfo 
                } = station;
                
                return {
                    ...stationInfo,
                    customer: customer.id ? customer : null // 客户不存在时返回null
                };
            });
        }
        
        // 执行总数查询（移除分页参数）
        const countParams = [...params];
        if (usePagination) {
            countParams.pop();
            countParams.pop();
        }
        const [countResult] = await db.query(countQuery, countParams);
        
        result = {
            code: 200,
            msg: "查询成功",
            data: stations,
            total: countResult.total
        };
    } catch (error) {
        result = {
            code: 500,
            msg: "查询失败: " + error.message,
            data: null,
            total: 0
        };
    }
    res.json(result);
});

router.post("/add", async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    var result = {};
    var data = req.body;
    
    try {
        // 验证必要参数
        if (!data.name || data.name.trim() === "") {
            result = {
                code: 400,
                msg: "站点名称不能为空"
            };
            res.send(JSON.stringify(result));
            return;
        }
        
        // 验证客户ID
        if (!data.customer_id || isNaN(Number(data.customer_id))) {
            result = {
                code: 400,
                msg: "请选择有效的客户"
            };
            res.send(JSON.stringify(result));
            return;
        }

        // 验证负责人联系方式
        if (!data.phone || data.phone.trim() === "") {
            result = {
                code: 400,
                msg: "负责人联系方式不能为空"
            };
            res.send(JSON.stringify(result));
            return;
        }
        
        // 验证手机号格式（11位数字）
        if (!/^1\d{10}$/.test(data.phone.trim())) {
            result = {
                code: 400,
                msg: "请输入有效的11位手机号码"
            };
            res.send(JSON.stringify(result));
            return;
        }

        // 验证站点所在地
        if (!data.city || data.city.trim() === "") {
            result = {
                code: 400,
                msg: "站点所在地不能为空"
            };
            res.send(JSON.stringify(result));
            return;
        }
        
        // 获取当前登录用户信息
        const { user_id } = req.auth;
        if (!user_id) {
            result = {
                code: 401,
                msg: "未获取到用户信息，请重新登录"
            };
            res.send(JSON.stringify(result));
            return;
        }
        
        // 验证客户是否存在
        const customerResult = await connection.execute(
            `SELECT id FROM customer WHERE id = ?`,
            [data.customer_id]
        );
        const existingCustomer = customerResult[0];
        
        if (existingCustomer.length === 0) {
            // 客户不存在
            result = {
                code: 400,
                msg: "所选客户不存在，请重新选择"
            };
            res.send(JSON.stringify(result));
            return;
        }
        
        // 检查同一客户下是否存在同名站点
        const nameResult = await connection.execute(
            `SELECT id FROM station WHERE name = ? AND customer_id = ?`,
            [data.name.trim(), data.customer_id]
        );
        const existingStation = nameResult[0];
        
        if (existingStation.length !== 0) {
            // 同一客户下站点名称已存在
            result = {
                code: 201,
                msg: "该客户下已存在同名站点，请修改名称"
            };
            res.send(JSON.stringify(result));
            return;
        }
        
        // 准备插入数据
        const stationName = data.name.trim();
        const customerId = data.customer_id;
        const phone = data.phone.trim();  // 负责人联系方式
        const city = data.city.trim();    // 站点所在地
        const createTime = getTime();     // 使用与客户新增相同的时间获取方法
        
        // 开启事务
        await db.beginTransaction(connection);
        
        // 执行数据库插入（新增phone和city字段）
        const insertResult = await connection.execute(
            `INSERT INTO station 
             (name, customer_id, phone, city, insert_user_id, create_time) 
             VALUES (?, ?, ?, ?, ?, ?)`,
            [stationName, customerId, phone, city, user_id, createTime]
        );
        
        // 准备操作日志
        const stationId = insertResult[0].insertId;
        const sql_words = `ID为${user_id}的用户为客户ID${customerId}新增了ID为${stationId}的站点（名称：${stationName}）`;
        
        // 插入操作日志
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?);`,
            [user_id, sql_words, createTime]
        );
        
        // 提交事务
        await db.commit(connection);
        
        // 返回成功结果
        result = {
            code: 200,
            msg: "站点新增成功"
        };
        res.send(JSON.stringify(result));
    } catch (error) {
        // 回滚事务
        await db.rollback(connection);
        console.error('新增站点错误:', error);
        result = {
            code: 500,
            msg: "服务器错误，新增站点失败"
        };
        res.send(JSON.stringify(result));
    } finally {
        // 释放连接
        connection.release();
    }
});

// 更新站点信息接口
router.post("/update", async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    var result = {};
    var data = req.body;
    
    try {
        // 验证必要参数
        if (!data.id) {
            result = {
                code: 400,
                msg: "站点ID不能为空"
            };
            res.json(result);
            return;
        }
        if (!data.name || data.name.trim() === "") {
            result = {
                code: 400,
                msg: "站点名称不能为空"
            };
            res.json(result);
            return;
        }
        if (!data.phone || data.phone.trim() === "") {
            result = {
                code: 400,
                msg: "负责人联系方式不能为空"
            };
            res.json(result);
            return;
        }
        // 简单验证手机号格式（11位数字）
        if (!/^1\d{10}$/.test(data.phone.trim())) {
            result = {
                code: 400,
                msg: "请输入有效的11位手机号码"
            };
            res.json(result);
            return;
        }
        if (!data.city || data.city.trim() === "") {
            result = {
                code: 400,
                msg: "所在地信息不能为空"
            };
            res.json(result);
            return;
        }
        
        // 获取当前登录用户信息
        const { user_id } = req.auth;
        if (!user_id) {
            result = {
                code: 401,
                msg: "未获取到用户信息，请重新登录"
            };
            res.json(result);
            return;
        }
        
        // 检查站点是否存在
        const [existingStation] = await connection.execute(
            `SELECT id, name, phone, city FROM station WHERE id = ?`,
            [data.id]
        );
        
        if (existingStation.length === 0) {
            result = {
                code: 404,
                msg: "未找到指定站点"
            };
            res.json(result);
            return;
        }
        
        // 准备更新数据
        const stationName = data.name.trim();
        const phone = data.phone.trim();
        const city = data.city.trim();
        const updateTime = getTime(); // 使用与新增相同的时间获取方法
        const originalStation = existingStation[0]; // 记录原始信息用于日志
        
        // 开启事务
        await db.beginTransaction(connection);
        
        // 执行更新操作
        await connection.execute(
            `UPDATE station 
             SET name = ?, phone = ?, city = ?, update_user_id = ?, update_time = ? 
             WHERE id = ?`,
            [stationName, phone, city, user_id, updateTime, data.id]
        );
        
        // 准备操作日志（记录更新前后的变化）
        const changeDetails = [];
        if (originalStation.name !== stationName) {
            changeDetails.push(`名称从"${originalStation.name}"修改为"${stationName}"`);
        }
        if (originalStation.phone !== phone) {
            changeDetails.push(`联系方式从"${originalStation.phone}"修改为"${phone}"`);
        }
        if (originalStation.city !== city) {
            changeDetails.push(`所在地从"${originalStation.city}"修改为"${city}"`);
        }
        
        const sql_words = `ID为${user_id}的用户更新了ID为${data.id}的站点信息：${changeDetails.join('，')}`;
        
        // 插入操作日志
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?);`,
            [user_id, sql_words, updateTime]
        );
        
        // 提交事务
        await db.commit(connection);
        
        // 返回成功结果
        result = {
            code: 200,
            msg: "站点信息更新成功"
        };
        res.json(result);
    } catch (error) {
        // 发生错误时回滚事务
        await db.rollback(connection);
        console.error("更新站点失败:", error); // 记录详细错误日志
        result = {
            code: 500,
            msg: "服务器错误，更新站点失败"
        };
        res.json(result);
    } finally {
        // 释放数据库连接
        connection.release();
    }
});
    

module.exports = router